using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 设备故障排名工具 - 查询设备故障次数、离线时长等排名信息
/// 覆盖场景：D-005 故障最多设备, D-006 离线历史设备, D-008 离线时长排行
/// </summary>
public class GetDeviceAlarmRankingTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetDeviceAlarmRankingTool> _logger;

    public string Name => "get_device_alarm_ranking";
    public string Description => "查询设备故障排名信息，包括故障次数、离线时长等。用于回答'故障最多的设备'、'离线时长排名'、'最近离线过的设备'等问题。";

    public GetDeviceAlarmRankingTool(
        IDatabaseExecutor executor,
        ILogger<GetDeviceAlarmRankingTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            // 获取参数
            int days = parameters.ContainsKey("days") ? Convert.ToInt32(parameters["days"]) : 30;
            int topN = parameters.ContainsKey("topN") ? Convert.ToInt32(parameters["topN"]) : 10;
            string rankType = parameters.ContainsKey("rankType") ? parameters["rankType"]?.ToString() ?? "fault_count" : "fault_count";
            string? deviceType = parameters.ContainsKey("deviceType") ? parameters["deviceType"]?.ToString() : null;

            // 构建SQL
            var sql = rankType switch
            {
                "offline_duration" => BuildOfflineDurationQuery(days, topN),
                "offline_count" => BuildOfflineCountQuery(days, topN, deviceType),
                _ => BuildFaultCountQuery(days, topN)
            };

            _logger.LogInformation("Executing device alarm ranking query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "设备故障排名查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["rankType"] = rankType,
                    ["days"] = days
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing device alarm ranking query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建故障次数排名查询
    /// </summary>
    private string BuildFaultCountQuery(int days, int topN)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    d.ID AS device_id,");
        sb.AppendLine("    d.Name AS device_name,");
        sb.AppendLine("    d.CustomCode AS device_code,");
        sb.AppendLine("    dt.Name AS device_type,");
        sb.AppendLine("    a.Name AS area_name,");
        sb.AppendLine("    COUNT(al.ID) AS fault_count,");
        sb.AppendLine("    MAX(al.AlarmCreateTime) AS last_fault_time,");
        sb.AppendLine("    MIN(al.AlarmCreateTime) AS first_fault_time,");
        sb.AppendLine("    CASE d.OnLineStatus");
        sb.AppendLine("        WHEN 1 THEN '在线'");
        sb.AppendLine("        WHEN 2 THEN '离线'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS current_status");
        sb.AppendLine("FROM TblAlarmLog al");
        sb.AppendLine("INNER JOIN TblDevice d ON al.DeviceId = d.ID");
        sb.AppendLine("LEFT JOIN TblDeviceType dt ON d.DeviceTypeId = dt.ID");
        sb.AppendLine("LEFT JOIN TblArea a ON d.StationAreaId = a.ID");
        sb.AppendLine($"WHERE al.AlarmCreateTime >= DATE_SUB(CURDATE(), INTERVAL {days} DAY)");
        sb.AppendLine("    AND d.IsValid = 1");
        sb.AppendLine("GROUP BY d.ID, d.Name, d.CustomCode, dt.Name, a.Name, d.OnLineStatus");
        sb.AppendLine("ORDER BY fault_count DESC");
        sb.AppendLine($"LIMIT {topN}");

        return sb.ToString();
    }

    /// <summary>
    /// 构建离线次数排名查询
    /// </summary>
    private string BuildOfflineCountQuery(int days, int topN, string? deviceType)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT DISTINCT");
        sb.AppendLine("    d.ID AS device_id,");
        sb.AppendLine("    d.Name AS device_name,");
        sb.AppendLine("    d.CustomCode AS device_code,");
        sb.AppendLine("    dt.Name AS device_type,");
        sb.AppendLine("    a.Name AS area_name,");
        sb.AppendLine("    COUNT(al.ID) AS offline_count,");
        sb.AppendLine("    MAX(al.AlarmCreateTime) AS last_offline_time,");
        sb.AppendLine("    MAX(al.AlarmRepairTime) AS last_recover_time,");
        sb.AppendLine("    CASE d.OnLineStatus");
        sb.AppendLine("        WHEN 1 THEN '在线'");
        sb.AppendLine("        WHEN 2 THEN '离线'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS current_status");
        sb.AppendLine("FROM TblAlarmLog al");
        sb.AppendLine("INNER JOIN TblDevice d ON al.DeviceId = d.ID");
        sb.AppendLine("LEFT JOIN TblDeviceType dt ON d.DeviceTypeId = dt.ID");
        sb.AppendLine("LEFT JOIN TblArea a ON d.StationAreaId = a.ID");
        sb.AppendLine("WHERE al.EventType = 2");
        sb.AppendLine($"    AND al.AlarmCreateTime >= DATE_SUB(CURDATE(), INTERVAL {days} DAY)");

        if (!string.IsNullOrEmpty(deviceType))
        {
            if (deviceType.Contains("摄像"))
            {
                sb.AppendLine("    AND (dt.Name LIKE '%宇视网络摄像机%' OR dt.Name LIKE '%摄像%')");
            }
            else
            {
                sb.AppendLine($"    AND dt.Name LIKE '%{deviceType}%'");
            }
        }

        sb.AppendLine("GROUP BY d.ID, d.Name, d.CustomCode, dt.Name, a.Name, d.OnLineStatus");
        sb.AppendLine("ORDER BY offline_count DESC, last_offline_time DESC");
        sb.AppendLine($"LIMIT {topN}");

        return sb.ToString();
    }

    /// <summary>
    /// 构建离线时长排名查询
    /// </summary>
    private string BuildOfflineDurationQuery(int days, int topN)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    d.ID AS device_id,");
        sb.AppendLine("    d.Name AS device_name,");
        sb.AppendLine("    d.CustomCode AS device_code,");
        sb.AppendLine("    dt.Name AS device_type,");
        sb.AppendLine("    a.Name AS area_name,");
        sb.AppendLine("    COUNT(al.ID) AS offline_times,");
        sb.AppendLine("    SUM(al.Duration) AS total_offline_seconds,");
        sb.AppendLine("    ROUND(SUM(al.Duration) / 3600, 2) AS total_offline_hours,");
        sb.AppendLine("    ROUND(SUM(al.Duration) / 86400, 2) AS total_offline_days,");
        sb.AppendLine("    MAX(al.Duration) AS max_offline_seconds,");
        sb.AppendLine("    ROUND(MAX(al.Duration) / 3600, 2) AS max_offline_hours,");
        sb.AppendLine("    MIN(al.AlarmCreateTime) AS first_offline_time,");
        sb.AppendLine("    MAX(al.AlarmCreateTime) AS last_offline_time,");
        sb.AppendLine("    CASE d.OnLineStatus");
        sb.AppendLine("        WHEN 1 THEN '在线'");
        sb.AppendLine("        WHEN 2 THEN '离线'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS current_status");
        sb.AppendLine("FROM TblAlarmLog al");
        sb.AppendLine("INNER JOIN TblDevice d ON al.DeviceId = d.ID");
        sb.AppendLine("LEFT JOIN TblDeviceType dt ON d.DeviceTypeId = dt.ID");
        sb.AppendLine("LEFT JOIN TblArea a ON d.StationAreaId = a.ID");
        sb.AppendLine("WHERE al.EventType = 2");
        sb.AppendLine($"    AND al.AlarmCreateTime >= DATE_SUB(CURDATE(), INTERVAL {days} DAY)");
        sb.AppendLine("    AND d.IsValid = 1");
        sb.AppendLine("GROUP BY d.ID, d.Name, d.CustomCode, dt.Name, a.Name, d.OnLineStatus");
        sb.AppendLine("ORDER BY total_offline_seconds DESC");
        sb.AppendLine($"LIMIT {topN}");

        return sb.ToString();
    }
}
